from flask import Flask, render_template, jsonify
import pandas as pd
import numpy as np
from datetime import datetime
import json

app = Flask(__name__)

# 读取数据
def load_data():
    customer_base = pd.read_csv('customer_base.csv')
    customer_behavior = pd.read_csv('customer_behavior_assets.csv')
    return customer_base, customer_behavior

# 主页路由
@app.route('/')
def dashboard():
    return render_template('dashboard2.html')

# API路由：获取顶部卡片数据（增加营销响应率）
@app.route('/api/overview')
def get_overview():
    customer_base, customer_behavior = load_data()
    
    total_customers = len(customer_base)
    total_assets = customer_behavior['total_assets'].sum()
    active_users = len(customer_behavior[
        customer_behavior['app_login_count'] > 0
    ].customer_id.unique())
    
    # 计算营销响应率 (只考虑有联系结果的客户)
    contacted_customers = customer_behavior[customer_behavior['contact_result'].notna()]
    marketing_response_rate = (contacted_customers['contact_result'] == '成功').mean() * 100 if len(contacted_customers) > 0 else 0
    
    return jsonify({
        'total_customers': total_customers,
        'total_assets': total_assets,
        'active_users': active_users,
        'marketing_response_rate': round(marketing_response_rate, 2)
    })

# API路由：客户基础分布
@app.route('/api/customer_distribution')
def get_customer_distribution():
    customer_base, _ = load_data()
    
    age_groups = pd.cut(customer_base['age'], 
                       bins=[0, 25, 35, 45, 55, 65, 100],
                       labels=['25岁以下', '26-35岁', '36-45岁', '46-55岁', '56-65岁', '65岁以上'])
    
    age_dist = age_groups.value_counts().to_dict()
    gender_dist = customer_base['gender'].value_counts().to_dict()
    occupation_dist = customer_base['occupation'].value_counts().head(10).to_dict()
    
    return jsonify({
        'age_distribution': age_dist,
        'gender_distribution': gender_dist,
        'occupation_distribution': occupation_dist
    })

# API路由：客户最近登录持续时间分析
@app.route('/api/login_duration')
def get_login_duration():
    _, customer_behavior = load_data()
    
    # 将时间字段转换为datetime类型
    customer_behavior['last_app_login_time'] = pd.to_datetime(customer_behavior['last_app_login_time'])
    
    # 计算登录持续时间（以分钟为单位）
    duration_bins = [0, 5, 15, 30, 60, 120, float('inf')]
    duration_labels = ['2小时以上', '1-2小时', '30-60分钟', '15-30分钟', '5-15分钟', '0-5分钟']
    
    # 按时段和持续时间统计
    time_periods = {
        '早间': (6, 11),
        '午间': (11, 14),
        '下午': (14, 18),
        '晚间': (18, 22),
        '深夜': (22, 6)
    }
    
    # 确保时间区间正确处理跨天情况
    def is_in_period(hour, period):
        start, end = time_periods[period]
        if start < end:
            return start <= hour < end
        else:  # 处理跨天的情况（如深夜）
            return hour >= start or hour < end
    
    # 初始化结果数据
    result_data = {
        'periods': list(time_periods.keys()),
        'durations': duration_labels,
        'data': []
    }
    
    result_data = {
        'periods': list(time_periods.keys()),
        'durations': duration_labels,
        'data': []
    }

    # 统计每个时间段的持续时间分布
    hours = customer_behavior['last_app_login_time'].dt.hour
    durations = customer_behavior['app_financial_view_time']
    
    # 对每个时间段进行统计
    for period in time_periods.keys():
        period_data = []
        
        # 筛选当前时间段的数据
        period_mask = hours.apply(lambda h: is_in_period(h, period))
        period_durations = durations[period_mask]
        
        # 计算各持续时间区间的用户数量（反转顺序以匹配标签顺序）
        for i in range(len(duration_bins)-1, 0, -1):
            count = len(period_durations[
                (period_durations >= duration_bins[i-1]) & 
                (period_durations < duration_bins[i])
            ])
            period_data.append(count)
        # 添加最后一个区间（2小时以上）
        count = len(period_durations[period_durations >= duration_bins[-2]])
        period_data.append(count)
        
        result_data['data'].append(period_data)
    
    return jsonify(result_data)

# API路由：产品持有情况多维度分析
@app.route('/api/product_holdings')
def get_product_holdings():
    _, customer_behavior = load_data()
    
    # 获取最近一个月的数据
    latest_month = customer_behavior['stat_month'].max()
    latest_data = customer_behavior[customer_behavior['stat_month'] == latest_month]
    
    # 统计各类产品的持有情况和金额
    products = {
        '存款': ('deposit_flag', 'deposit_balance'),
        '理财': ('financial_flag', 'financial_balance'),
        '基金': ('fund_flag', 'fund_balance'),
        '保险': ('insurance_flag', 'insurance_balance')
    }
    
    result_data = []
    total_customers = len(latest_data)
    
    for product_name, (flag_col, balance_col) in products.items():
        holders = latest_data[flag_col].sum()
        avg_balance = latest_data[latest_data[flag_col] == True][balance_col].mean()
        penetration = (holders / total_customers) * 100
        
        result_data.append({
            'product': product_name,
            'holders': int(holders),
            'penetration': round(penetration, 2),
            'avg_balance': round(avg_balance if not pd.isna(avg_balance) else 0, 2)
        })
    
    # 统计产品组合情况
    latest_data['product_combination'] = latest_data.apply(
        lambda x: sum([
            x['deposit_flag'],
            x['financial_flag'],
            x['fund_flag'],
            x['insurance_flag']
        ]),
        axis=1
    )
    
    combination_stats = latest_data['product_combination'].value_counts().sort_index()
    
    return jsonify({
        'product_details': result_data,
        'combination_stats': {
            'labels': [f'{i}个产品' for i in combination_stats.index],
            'values': combination_stats.values.tolist()
        }
    })

# API路由：营销效果漏斗分析
@app.route('/api/marketing_funnel')
def get_marketing_funnel():
    _, customer_behavior = load_data()
    
    # 获取最近一个月的数据
    latest_month = customer_behavior['stat_month'].max()
    latest_data = customer_behavior[customer_behavior['stat_month'] == latest_month]
    
    # 计算漏斗各环节数据
    total_customers = len(latest_data)  # 总客户数
    
    # 产品购买环节
    product_customers = len(latest_data[latest_data['product_count'] > 0])
    
    # 营销触达环节（有被联系过的客户）
    reached_customers = len(latest_data[latest_data['last_contact_time'].notna()])
    
    # 客户响应环节（客户有反馈）
    responded_customers = len(latest_data[
        latest_data['contact_result'].isin(['success', 'rejected', 'considering'])
    ])
    
    # 成功联系环节（成功转化）
    success_customers = len(latest_data[latest_data['contact_result'] == 'success'])
    
    # 计算转化率
    funnel_data = [
        {'value': total_customers, 'name': '目标客户', 'rate': 100},
        {'value': product_customers, 'name': '产品购买', 'rate': round(product_customers/total_customers*100, 2)},
        {'value': reached_customers, 'name': '营销触达', 'rate': round(reached_customers/total_customers*100, 2)},
        {'value': responded_customers, 'name': '客户响应', 'rate': round(responded_customers/total_customers*100, 2)},
        {'value': success_customers, 'name': '成功转化', 'rate': round(success_customers/total_customers*100, 2)}
    ]
    
    # 计算月度趋势
    monthly_trend = customer_behavior.groupby('stat_month').agg({
        'contact_result': lambda x: (x == 'success').mean() * 100,  # 转化率
        'customer_id': 'count'  # 接触客户数
    }).round(2)
    
    return jsonify({
        'funnel': funnel_data,
        'trend': {
            'months': monthly_trend.index.tolist(),
            'conversion_rates': monthly_trend['contact_result'].tolist(),
            'customer_counts': monthly_trend['customer_id'].tolist()
        }
    })

# API路由：客户资产提升趋势
@app.route('/api/asset_growth')
def get_asset_growth():
    _, customer_behavior = load_data()
    
    # 计算每个客户的资产变化
    asset_growth = customer_behavior.groupby(['customer_id', 'stat_month'])['total_assets'].last()
    asset_growth = asset_growth.unstack()
    
    # 计算增长率
    months = sorted(customer_behavior['stat_month'].unique())
    growth_rates = []
    
    for i in range(1, len(months)):
        prev_month = months[i-1]
        curr_month = months[i]
        
        # 计算环比增长率
        growth_rate = (
            (asset_growth[curr_month] - asset_growth[prev_month]) / 
            asset_growth[prev_month]
        ).mean() * 100
        
        growth_rates.append({
            'month': curr_month,
            'growth_rate': round(growth_rate, 2)
        })
    
    # 计算各资产等级的平均资产变化趋势
    asset_level_trends = customer_behavior.groupby(['stat_month', 'asset_level'])['total_assets'].mean()
    asset_level_trends = asset_level_trends.unstack().to_dict()
    
    return jsonify({
        'growth_rates': growth_rates,
        'asset_level_trends': {
            k: [round(v, 2) for v in values.values()]
            for k, values in asset_level_trends.items()
        },
        'months': months
    })

if __name__ == '__main__':
    app.run(debug=True, port=5000)
